
--Create database

USE [master]
GO
IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE name = N'BankElKheir')
BEGIN
CREATE DATABASE [BankElKheir] COLLATE Arabic_CI_AS
END
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'BankElKheir', @new_cmptlevel=100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [BankElKheir].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [BankElKheir] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [BankElKheir] SET ANSI_NULLS OFF
GO
ALTER DATABASE [BankElKheir] SET ANSI_PADDING OFF
GO
ALTER DATABASE [BankElKheir] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [BankElKheir] SET ARITHABORT OFF
GO
ALTER DATABASE [BankElKheir] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [BankElKheir] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [BankElKheir] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [BankElKheir] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [BankElKheir] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [BankElKheir] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [BankElKheir] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [BankElKheir] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [BankElKheir] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [BankElKheir] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [BankElKheir] SET DISABLE_BROKER
GO
ALTER DATABASE [BankElKheir] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [BankElKheir] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [BankElKheir] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BankElKheir] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BankElKheir] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [BankElKheir] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [BankElKheir] SET READ_WRITE
GO
ALTER DATABASE [BankElKheir] SET RECOVERY FULL
GO
ALTER DATABASE [BankElKheir] SET MULTI_USER
GO
ALTER DATABASE [BankElKheir] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [BankElKheir] SET DB_CHAINING OFF
GO

USE [BankElKheir]
GO

--Create Roles
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'public' AND type = 'R')
	CREATE ROLE [public] AUTHORIZATION [dbo]
GO

--Database Schemas

USE [BankElKheir]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'dbo')
EXEC sys.sp_executesql N'CREATE SCHEMA [dbo] AUTHORIZATION [dbo]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'guest')
EXEC sys.sp_executesql N'CREATE SCHEMA [guest] AUTHORIZATION [guest]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'INFORMATION_SCHEMA')
EXEC sys.sp_executesql N'CREATE SCHEMA [INFORMATION_SCHEMA] AUTHORIZATION [INFORMATION_SCHEMA]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'sys')
EXEC sys.sp_executesql N'CREATE SCHEMA [sys] AUTHORIZATION [sys]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_owner')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_owner] AUTHORIZATION [db_owner]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_accessadmin] AUTHORIZATION [db_accessadmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_securityadmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_securityadmin] AUTHORIZATION [db_securityadmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_ddladmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_ddladmin] AUTHORIZATION [db_ddladmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_backupoperator')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_backupoperator] AUTHORIZATION [db_backupoperator]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datareader')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_datareader] AUTHORIZATION [db_datareader]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datawriter')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_datawriter] AUTHORIZATION [db_datawriter]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatareader')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_denydatareader] AUTHORIZATION [db_denydatareader]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatawriter')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_denydatawriter] AUTHORIZATION [db_denydatawriter]'
GO

--Table dbo.Akhesaye

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Akhesaye] (
	[Name] [nvarchar](255) NOT NULL);
GO

INSERT INTO [dbo].[Akhesaye] ([Name])
	VALUES (N'احمد الصواف')

GO
INSERT INTO [dbo].[Akhesaye] ([Name])
	VALUES (N'احمد عرابى')

GO
INSERT INTO [dbo].[Akhesaye] ([Name])
	VALUES (N'احمد علاء')

GO
INSERT INTO [dbo].[Akhesaye] ([Name])
	VALUES (N'يبسبسي')

GO

--Table dbo.Client

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Client] (
	[C_Id] [int] NOT NULL,
	[C_Name] [nvarchar](255) NULL,
	[C_Gender] [nvarchar](255) NULL,
	[C_Phone] [nvarchar](255) NULL,
	[C_Mobile] [nvarchar](255) NULL,
	[C_Region] [nvarchar](255) NULL,
	[C_Address] [nvarchar](255) NULL);
GO

INSERT INTO [dbo].[Client] ([C_Id], [C_Name], [C_Gender], [C_Phone], [C_Mobile], [C_Region], [C_Address])
	VALUES (100, N'مصطفي ', N'ذكر', N'4567890', N'456789', N'النحلة', N'المنشية')

GO
INSERT INTO [dbo].[Client] ([C_Id], [C_Name], [C_Gender], [C_Phone], [C_Mobile], [C_Region], [C_Address])
	VALUES (132, N'gfdfgdfgd', N'ذكر', N'56456456546546456', N'546546546456', N'jhhj', N'nfhjn')

GO
INSERT INTO [dbo].[Client] ([C_Id], [C_Name], [C_Gender], [C_Phone], [C_Mobile], [C_Region], [C_Address])
	VALUES (200, N'نتا', N'انثى', N'67890', N'67890', N'لاتنمك', N'ىةوزظ')

GO
INSERT INTO [dbo].[Client] ([C_Id], [C_Name], [C_Gender], [C_Phone], [C_Mobile], [C_Region], [C_Address])
	VALUES (324, N'يبيسب', N'انثى', N'4532', N'324', N'سيبس', N'فقف')

GO
INSERT INTO [dbo].[Client] ([C_Id], [C_Name], [C_Gender], [C_Phone], [C_Mobile], [C_Region], [C_Address])
	VALUES (4354354, N'jkhjh', N'ذكر', N'434535', N'6575757575', N'kjhj', N'gfgjhg')

GO

--Table dbo.Damn

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Damn] (
	[D_Id] [int] NOT NULL IDENTITY (1, 1),
	[D_Name] [nvarchar](255) NULL,
	[D_Relation] [nvarchar](255) NULL,
	[D_Job] [nvarchar](255) NULL,
	[D_SalaryPlace] [nvarchar](255) NULL,
	[D_Phone] [nvarchar](255) NULL,
	[D_Moblie] [nvarchar](255) NULL,
	[D_Address] [nvarchar](255) NULL,
	[D_IdNum] [nvarchar](50) NULL,
	[R_Id] [int] NULL,
	[C_Id] [int] NULL);
GO

SET IDENTITY_INSERT [dbo].[Damn] ON
GO
INSERT INTO [dbo].[Damn] ([D_Id], [D_Name], [D_Relation], [D_Job], [D_SalaryPlace], [D_Phone], [D_Moblie], [D_Address], [D_IdNum], [R_Id], [C_Id])
	VALUES (50, N'مصطفي', N'اخ', N'منت', N'منت', N'567890', N'567890', N'كمنت', N'34567890', 78, NULL)

GO
INSERT INTO [dbo].[Damn] ([D_Id], [D_Name], [D_Relation], [D_Job], [D_SalaryPlace], [D_Phone], [D_Moblie], [D_Address], [D_IdNum], [R_Id], [C_Id])
	VALUES (53, N'jhhgjgjghj', N'hgjhgjhgjhg', N'hgjhgjhgj', N'hgjhgjghjghj', N'567675554', N'565464565463', N'hgjhgjghjgh', N'6546546546546', 643645, NULL)

GO
INSERT INTO [dbo].[Damn] ([D_Id], [D_Name], [D_Relation], [D_Job], [D_SalaryPlace], [D_Phone], [D_Moblie], [D_Address], [D_IdNum], [R_Id], [C_Id])
	VALUES (54, N'gjjhjgj', N'hjg', N'iyugygh', N'7676', N'7865756', N'676678', N'jhggjkhgfgh', N'7866786757456', 676676, 4354354)

GO
INSERT INTO [dbo].[Damn] ([D_Id], [D_Name], [D_Relation], [D_Job], [D_SalaryPlace], [D_Phone], [D_Moblie], [D_Address], [D_IdNum], [R_Id], [C_Id])
	VALUES (55, N'jhjghfgfg', N'jhgfg', N'bhgfhj', N'khgfhg', N'8765677', N'7576546654', N'hghfhgfg', N'9876545678', 676676, 4354354)

GO
SET IDENTITY_INSERT [dbo].[Damn] OFF
GO

--Table dbo.Request

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Request] (
	[R_Id] [int] NOT NULL,
	[Loan_Type] [nvarchar](255) NULL,
	[Loan_Value] [int] NULL,
	[MonthlyPayment] [int] NULL,
	[NomberOfMonths] [int] NULL,
	[Client_Id] [int] NULL);
GO

INSERT INTO [dbo].[Request] ([R_Id], [Loan_Type], [Loan_Value], [MonthlyPayment], [NomberOfMonths], [Client_Id])
	VALUES (78, N'زواج', 100, 40, 3, 200)

GO
INSERT INTO [dbo].[Request] ([R_Id], [Loan_Type], [Loan_Value], [MonthlyPayment], [NomberOfMonths], [Client_Id])
	VALUES (344, N'سداد ديون', 234234234, 24234, 9666, 324)

GO
INSERT INTO [dbo].[Request] ([R_Id], [Loan_Type], [Loan_Value], [MonthlyPayment], [NomberOfMonths], [Client_Id])
	VALUES (643645, N'زواج', 1000, 100, 10, 132)

GO
INSERT INTO [dbo].[Request] ([R_Id], [Loan_Type], [Loan_Value], [MonthlyPayment], [NomberOfMonths], [Client_Id])
	VALUES (676676, N'اخرى', 66666, 5555, 13, 4354354)

GO

--Table dbo.Search

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Search] (
	[Search_Id] [int] NOT NULL,
	[S_Desision] [nvarchar](255) NULL,
	[S_Reason] [nvarchar](255) NULL,
	[Search_Akhesaye] [nvarchar](255) NULL);
GO

INSERT INTO [dbo].[Search] ([Search_Id], [S_Desision], [S_Reason], [Search_Akhesaye])
	VALUES (78, N'رفض الطلب', N'', N'احمد الصواف')

GO
INSERT INTO [dbo].[Search] ([Search_Id], [S_Desision], [S_Reason], [Search_Akhesaye])
	VALUES (344, N'رفض الطلب', N'سيبيس', N'احمد علاء')

GO
INSERT INTO [dbo].[Search] ([Search_Id], [S_Desision], [S_Reason], [Search_Akhesaye])
	VALUES (643645, N'تحويل الطلب للتمويل', N'jhk,jhj', N'احمد علاء')

GO
INSERT INTO [dbo].[Search] ([Search_Id], [S_Desision], [S_Reason], [Search_Akhesaye])
	VALUES (676676, N'تحويل الطلب للتمويل', N'', N'احمد الصواف')

GO

--Table dbo.Tamweel

USE [BankElKheir]
GO

--Create table and its columns
CREATE TABLE [dbo].[Tamweel] (
	[T_Id] [int] NOT NULL,
	[T_Decission] [nvarchar](255) NULL,
	[T_Reason] [nvarchar](255) NULL,
	[T_Akhesaye] [nvarchar](255) NULL);
GO

INSERT INTO [dbo].[Tamweel] ([T_Id], [T_Decission], [T_Reason], [T_Akhesaye])
	VALUES (78, N'قبول الطلب', N'', N'احمد علاء')

GO
INSERT INTO [dbo].[Tamweel] ([T_Id], [T_Decission], [T_Reason], [T_Akhesaye])
	VALUES (643645, N'تأجيل الطلب', N'hghg', N'احمد علاء')

GO
INSERT INTO [dbo].[Tamweel] ([T_Id], [T_Decission], [T_Reason], [T_Akhesaye])
	VALUES (676676, N'قبول الطلب', N'', N'احمد علاء')

GO

--Indexes of table dbo.Akhesaye
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Akhesaye] ADD CONSTRAINT [aaaaaAkhesaye_PK] PRIMARY KEY NONCLUSTERED ([Name]) 
GO

--Indexes of table dbo.Client
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [aaaaaClient_PK] PRIMARY KEY NONCLUSTERED ([C_Id]) 
GO

--Indexes of table dbo.Damn
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Damn] ADD CONSTRAINT [aaaaaDamn_PK] PRIMARY KEY NONCLUSTERED ([D_Id]) 
GO

CREATE NONCLUSTERED INDEX [ClientDamn] ON [dbo].[Damn]  ([C_Id]) 
GO

CREATE NONCLUSTERED INDEX [R_Id] ON [dbo].[Damn]  ([R_Id]) 
GO

CREATE NONCLUSTERED INDEX [RequestDamn] ON [dbo].[Damn]  ([R_Id]) 
GO

--Indexes of table dbo.Request
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Request] ADD CONSTRAINT [aaaaaRequest_PK] PRIMARY KEY NONCLUSTERED ([R_Id]) 
GO

CREATE NONCLUSTERED INDEX [Client_Id] ON [dbo].[Request]  ([Client_Id]) 
GO

CREATE NONCLUSTERED INDEX [ClientRequest] ON [dbo].[Request]  ([Client_Id]) 
GO

--Indexes of table dbo.Search
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Search] ADD CONSTRAINT [aaaaaSearch_PK] PRIMARY KEY NONCLUSTERED ([Search_Id]) 
GO

CREATE NONCLUSTERED INDEX [AkhesayeSearch] ON [dbo].[Search]  ([Search_Akhesaye]) 
GO

CREATE UNIQUE NONCLUSTERED INDEX [RequestSearch] ON [dbo].[Search]  ([Search_Id]) 
GO

--Indexes of table dbo.Tamweel
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Tamweel] ADD CONSTRAINT [aaaaaTamweel_PK] PRIMARY KEY NONCLUSTERED ([T_Id]) 
GO

CREATE NONCLUSTERED INDEX [AkhesayeTamweel] ON [dbo].[Tamweel]  ([T_Akhesaye]) 
GO

CREATE UNIQUE NONCLUSTERED INDEX [RequestTamweel] ON [dbo].[Tamweel]  ([T_Id]) 
GO

--Foreign Keys

USE [BankElKheir]
GO
ALTER TABLE [dbo].[Damn] WITH CHECK ADD CONSTRAINT [Damn_FK00] 
	FOREIGN KEY ([C_Id]) REFERENCES [dbo].[Client] ([C_Id])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Damn] CHECK CONSTRAINT [Damn_FK00]
GO
ALTER TABLE [dbo].[Damn] WITH CHECK ADD CONSTRAINT [Damn_FK01] 
	FOREIGN KEY ([R_Id]) REFERENCES [dbo].[Request] ([R_Id])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Damn] CHECK CONSTRAINT [Damn_FK01]
GO
ALTER TABLE [dbo].[Search] WITH CHECK ADD CONSTRAINT [Search_FK00] 
	FOREIGN KEY ([Search_Akhesaye]) REFERENCES [dbo].[Akhesaye] ([Name])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Search] CHECK CONSTRAINT [Search_FK00]
GO
ALTER TABLE [dbo].[Search] WITH CHECK ADD CONSTRAINT [Search_FK01] 
	FOREIGN KEY ([Search_Id]) REFERENCES [dbo].[Request] ([R_Id])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Search] CHECK CONSTRAINT [Search_FK01]
GO
ALTER TABLE [dbo].[Tamweel] WITH CHECK ADD CONSTRAINT [Tamweel_FK00] 
	FOREIGN KEY ([T_Akhesaye]) REFERENCES [dbo].[Akhesaye] ([Name])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Tamweel] CHECK CONSTRAINT [Tamweel_FK00]
GO
ALTER TABLE [dbo].[Tamweel] WITH CHECK ADD CONSTRAINT [Tamweel_FK01] 
	FOREIGN KEY ([T_Id]) REFERENCES [dbo].[Request] ([R_Id])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Tamweel] CHECK CONSTRAINT [Tamweel_FK01]
GO
